package com.iplant.basic.serviceImpl.dao.bms;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.iplant.basic.service.po.bfc.auth.BFCModuleAuth;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;

import com.iplant.base.po.OutResult;
import com.iplant.base.po.Pagination;
import com.iplant.base.po.enums.DBSchemas;
import com.iplant.base.po.enums.ExceptionTypes;
import com.iplant.base.utils.general.StringUtils;
import com.iplant.base.utils.sql.BaseDAO;
import com.iplant.basic.service.po.bms.BMSEmployee;
import com.iplant.basic.service.po.bms.BMSRole;
import com.iplant.basic.service.po.bms.BMSRoleItem;
import com.iplant.basic.utils.Constants;

public class BMSRoleDAO extends BaseDAO {
    private static BMSRoleDAO Instance = null;
    private static Logger logger = LoggerFactory.getLogger(BMSRoleDAO.class);

    private BMSRoleDAO() {
        super();
        setTableName("mbs_role");
    }

    public static BMSRoleDAO getInstance() {
        if (Instance == null)
            Instance = new BMSRoleDAO();
        return Instance;
    }

    public BMSRole BMS_AddRole(BMSEmployee wLoginUser, BMSRole wRole, OutResult<Integer> wErrorCode) {
        wErrorCode.set(0);

        try {

            String wSQLText = "";

            wSQLText = StringUtils.Format("Insert Into {0}.mbs_role", super.getDBName())
                    + "(Name,EditorID,CreatorID,DepartmentID,Active,CreateTime,EditTime,Description)"
                    + " Values(:Name,:EditorID,:CreatorID,:DepartmentID,:Active,:CreateTime,:EditTime,:Description)";
            Map<String, Object> wParams = new HashMap<String, Object>();
            wParams.clear();
            wParams.put("Name", wRole.Name);
            wParams.put("CreatorID", wRole.getID());
            wParams.put("EditorID", wRole.getID());
            wParams.put("DepartmentID", wRole.DepartmentID);
            wParams.put("CreateTime", Calendar.getInstance());
            wParams.put("EditTime", Calendar.getInstance());
            wParams.put("Active", wRole.Active);
            wParams.put("Description", wRole.Description);

            super.update(wSQLText, wParams);

        } catch (Exception ex) {
            logger.error("BMS_AddRole", ex);
            wErrorCode.set(ExceptionTypes.DBSQL.getValue());
        }

        return wRole;
    }

    private List<BMSRole> BMS_GetRoleList(BMSEmployee wLoginUser, int wID, List<Integer> wDepartmentID, String wName,
                                          int wUserID, int wActive, Pagination wPagination,
                                          OutResult<Integer> wErrorCode) {
        wErrorCode.set(0);
        List<BMSRole> wRoleList = new ArrayList<BMSRole>();

        try {

            if (wDepartmentID == null)
                wDepartmentID = new ArrayList<Integer>();

            wDepartmentID.removeIf(p -> p <= 0);

            String wSQLText = StringUtils.Format(
                    "select t.*,t2.Name as DepartmentName ,t3.Name as CreatorName ,"
                            + " t4.Name as EditorName,group_concat(distinct t5.UserID)  as EmployeeIDList  FROM {0}" +
                            ".mbs_role t"
                            + " left join {0}.bms_department t2 on t.DepartmentID=t2.ID "
                            + " left join {0}.mbs_user t3 on t.CreatorID=t3.ID "
                            + " left join {0}.mbs_user t4 on t.EditorID=t4.ID "
                            + " left join {0}.mbs_roleuser t5 on t.ID=t5.RoleID "
                            + " where  t.ID>0 AND (:ID<=0 or t.ID=:ID) and (:Active< 0 or t.Active=:Active) "
                            + " AND (:DepartmentID= '''' or  t.DepartmentID in ({1})) "
                            + " AND ( :UserID<=0 or t5.UserID=:UserID) "
                            + " AND (:Name = '''' or  t.Name like :Name) group by t.ID ",
                    super.getDBName(), wDepartmentID.size() > 0 ? StringUtils.Join(",", wDepartmentID) : "0");
            Map<String, Object> wParams = new HashMap<String, Object>();
            wParams.put("ID", wID);
            wParams.put("Active", wActive);
            wParams.put("UserID", wUserID);
            wParams.put("DepartmentID", StringUtils.Join(",", wDepartmentID));
            wParams.put("Name", StringUtils.isEmpty(wName) ? "" : ("%" + wName + "%"));
            BMSRole wBMSRole = null;
            List<Map<String, Object>> wQueryResultList = super.queryForList(wSQLText, wParams);
            for (Map<String, Object> wSqlDataReader : wQueryResultList) {
                wBMSRole = new BMSRole();
                wBMSRole.ID = StringUtils.parseInt(wSqlDataReader.get("ID"));
                wBMSRole.Code = StringUtils.parseString(wSqlDataReader.get("Code"));
                wBMSRole.Name = StringUtils.parseString(wSqlDataReader.get("Name"));
                wBMSRole.CreatorID = StringUtils.parseInt(wSqlDataReader.get("CreatorID"));
                wBMSRole.EditorID = StringUtils.parseInt(wSqlDataReader.get("EditorID"));
                wBMSRole.CreatorName = StringUtils.parseString(wSqlDataReader.get("CreatorName"));
                wBMSRole.EditorName = StringUtils.parseString(wSqlDataReader.get("EditorName"));
                wBMSRole.CreateTime = StringUtils.parseCalendar(wSqlDataReader.get("CreateTime"));
                wBMSRole.EditTime = StringUtils.parseCalendar(wSqlDataReader.get("EditTime"));
                wBMSRole.DepartmentID = StringUtils.parseInt(wSqlDataReader.get("DepartmentID"));
                wBMSRole.DepartmentName = StringUtils.parseString(wSqlDataReader.get("DepartmentName"));
                wBMSRole.Description = StringUtils.parseString(wSqlDataReader.get("Description"));
                wBMSRole.Active = StringUtils.parseInt(wSqlDataReader.get("Active"));

                wBMSRole.EmployeeIDList = StringUtils.parseIntList(wSqlDataReader.get("EmployeeIDList"), ",");
                wBMSRole.EmployeeCount = wBMSRole.EmployeeIDList.size();
                if (StringUtils.isEmpty(wBMSRole.Code)) {
                    wBMSRole.Code = StringUtils.Format("POS{0}", String.format("%07d", wBMSRole.ID));
                }
                wRoleList.add(wBMSRole);
            }

        } catch (Exception ex) {
            wErrorCode.set(ExceptionTypes.DBSQL.getValue());
            logger.error("BMS_GetRoleList", ex);
        }

        return wRoleList;
    }

    public List<BMSRole> BMS_GetRoleList(BMSEmployee wLoginUser, List<Integer> wDepartmentID, String wName, int wUserID,
                                         int wActive, Pagination wPagination, OutResult<Integer> wErrorCode) {
        wErrorCode.set(0);
        List<BMSRole> wRoleList = new ArrayList<BMSRole>();

        try {
            wRoleList = this.BMS_GetRoleList(wLoginUser, -1, wDepartmentID, wName, wUserID, wActive, wPagination,
                    wErrorCode);
        } catch (Exception ex) {
            wErrorCode.set(ExceptionTypes.DBSQL.getValue());
            logger.error("BMS_GetRoleList", ex);
        }

        return wRoleList;
    }

    public BMSRole BMS_GetRoleByID(BMSEmployee wLoginUser, int wID, OutResult<Integer> wErrorCode) {
        wErrorCode.set(0);
        BMSRole wResult = new BMSRole();
        try {
            if (wID <= 0)
                return wResult;
            List<BMSRole> wList = this.BMS_GetRoleList(wLoginUser, wID, null, "", -1, -1, Pagination.getDefault(),
                    wErrorCode);
            if (wList != null && wList.size() > 0)
                wResult = wList.get(0);
        } catch (Exception ex) {
            wErrorCode.set(ExceptionTypes.DBSQL.getValue());
            logger.error("BMS_GetRoleList", ex);
        }

        return wResult;
    }

    public void BMS_RemoveRole(BMSEmployee wLoginUser, int wID, OutResult<Integer> wErrorCode) {
        wErrorCode.set(0);

        try {

            String wSQLText = "";
            Map<String, Object> wParams = new HashMap<String, Object>();

            BMSRole wBMSRole = this.BMS_GetRoleByID(wLoginUser, wID, wErrorCode);
            if (wBMSRole == null || wBMSRole.ID <= 0 || wBMSRole.Active != 0) {
                return;
            }
            wSQLText = StringUtils.Format("Delete from {0}.mbs_role where ID=:ID and Active = 0; ",
                    super.getDBName());

            wParams.clear();
            wParams.put("ID", wID);
            super.update(wSQLText, wParams);

            wSQLText = StringUtils.Format("delete from {0}.mbs_roleuser", super.getDBName())
                    + " where RoleID=:RoleID";
            wParams.clear();
            wParams.put("RoleID", wID);
            super.update(wSQLText, wParams);

        } catch (Exception ex) {
            logger.error("BMS_RemoveRole", ex);
            wErrorCode.set(ExceptionTypes.DBSQL.getValue());
        }

    }

    public int BMS_SaveRole(BMSEmployee wLoginUser, BMSRole wRole, OutResult<Integer> wErrorCode) {
        wErrorCode.set(0);

        try {

            String wSQLText = "";
            Map<String, Object> wParams = new HashMap<String, Object>();
            wSQLText = StringUtils.Format("Update {0}.mbs_role", super.getDBName())
                    + " set Code=:Code,Name=:Name,DepartmentID=:DepartmentID,EditorID=:EditorID,EditTime=:EditTime," +
                    "Description=:Description where ID=:ID ;";

            wParams.clear();
            wParams.put("Name", wRole.Name);
            wParams.put("EditorID", wLoginUser.getID());
            wParams.put("EditTime", Calendar.getInstance());
            wParams.put("DepartmentID", wRole.DepartmentID);
            wParams.put("Description", wRole.Description);
            wParams.put("ID", wRole.ID);
            wParams.put("Code", StringUtils.Format("POS{0}", String.format("%07d", wRole.ID)));
            super.update(wSQLText, wParams);

        } catch (Exception ex) {

            logger.error("BMS_SaveRole", ex);
            wErrorCode.set(ExceptionTypes.DBSQL.getValue());
        }
        return wErrorCode.Result;
    }

    public int BMS_DisableRole(BMSEmployee wLoginUser, int wID, OutResult<Integer> wErrorCode) {
        wErrorCode.set(0);

        try {

            String wSQLText = StringUtils.Format("Update {0}.mbs_role", super.getDBName())
                    + " set Code=:Code, EditorID=:EditorID,EditTime=:EditTime,Active=2 where ID=:ID";
            Map<String, Object> wParams = new HashMap<String, Object>();
            wParams.clear();
            wParams.put("EditorID", wLoginUser.getID());
            wParams.put("EditTime", Calendar.getInstance());
            wParams.put("ID", wID);

            wParams.put("Code", StringUtils.Format("POS{0}", String.format("%07d", wID)));
            super.update(wSQLText, wParams);

        } catch (Exception ex) {

            logger.error("BMS_DisableRole", ex);
            wErrorCode.set(ExceptionTypes.DBSQL.getValue());
        }
        return wErrorCode.Result;
    }

    public int BMS_ActiveRole(BMSEmployee wLoginUser, int wID, OutResult<Integer> wErrorCode) {
        wErrorCode.set(0);

        try {
            String wSQLText = StringUtils.Format("Update {0}.mbs_role", super.getDBName())
                    + " set Code=:Code, EditorID=:EditorID,EditTime=:EditTime,Active=1 where ID=:ID";
            Map<String, Object> wParams = new HashMap<String, Object>();
            wParams.clear();
            wParams.put("EditorID", wLoginUser.getID());
            wParams.put("EditTime", Calendar.getInstance());
            wParams.put("ID", wID);

            wParams.put("Code", StringUtils.Format("POS{0}", String.format("%07d", wID)));
            super.update(wSQLText, wParams);
        } catch (Exception ex) {

            logger.error("BMS_ActiveRole", ex);
            wErrorCode.set(ExceptionTypes.DBSQL.getValue());
        }
        return wErrorCode.Result;
    }

    /**
     * 角色权限
     *
     * @param wLoginUser
     * @param wRoleID
     * @param wFunctionList
     * @param wErrorCode
     * @return
     */
    public int BMS_SaveRoleFunctionList(BMSEmployee wLoginUser, int wRoleID, List<BMSRoleItem> wFunctionList,
                                        OutResult<Integer> wErrorCode) {
        wErrorCode.set(0);

        try {

            String wSQLText = "";
            Map<String, Object> wParams = new HashMap<String, Object>();
            // Step01:Delete
            if (wRoleID > 0) {
                wSQLText = StringUtils.Format("delete from {0}.mbs_rolefunction", super.getDBName())
                        + " where  RoleID=:RoleID";
                wParams.clear();
                wParams.put("RoleID", wRoleID);
                super.update(wSQLText, wParams);
            }
            // Step02:

            List<String> wValueStringList = new ArrayList<String>();
            for (BMSRoleItem wFunctionRole : wFunctionList) {
                wValueStringList.add(StringUtils.Format("({0},{1})", wFunctionRole.RoleID, wFunctionRole.FunctionID));
            }
            if (wValueStringList.size() > 0) {
                wSQLText = StringUtils.Format(
                        "Insert Into {0}.mbs_rolefunction" + "(RoleID,FunctionID) " + " Values {1}",
                        super.getDBName(), StringUtils.Join(",", wValueStringList));
                super.update(wSQLText, wParams);
            }

        } catch (Exception ex) {
            logger.error("BMS_SaveRoleFunctionList", ex);
            wErrorCode.set(ExceptionTypes.DBSQL.getValue());
        }
        return wErrorCode.Result;
    }

    public int BMS_SaveRoleRangeList(BMSEmployee wLoginUser, int wRoleID, List<BMSRoleItem> wFunctionList,
                                     OutResult<Integer> wErrorCode) {
        wErrorCode.set(0);

        try {

            String wSQLText = "";
            Map<String, Object> wParams = new HashMap<String, Object>();
            // Step01:Delete
            if (wRoleID > 0) {
                wSQLText = StringUtils.Format("delete from {0}.mbs_rolerange", super.getDBName())
                        + " where RoleID=:RoleID";
                wParams.clear();
                wParams.put("RoleID", wRoleID);
                super.update(wSQLText, wParams);
            }
            // Step02:
            for (BMSRoleItem wFunctionRole : wFunctionList) {
                wSQLText = StringUtils.Format("Insert Into {0}.mbs_rolerange", super.getDBName())
                        + "(RoleID,FunctionID,TypeID) " + " Values(:RoleID,:FunctionID,:TypeID)";
                wParams.clear();
                wParams.put("RoleID", wFunctionRole.RoleID);
                wParams.put("FunctionID", wFunctionRole.FunctionID);
                wParams.put("TypeID", wFunctionRole.TypeID);
                super.update(wSQLText, wParams);
            }

        } catch (Exception ex) {

            logger.error("BMS_SaveRoleRangeList", ex);
            wErrorCode.set(ExceptionTypes.DBSQL.getValue());
        }
        return wErrorCode.Result;
    }

    public int BMS_SaveRoleUserList(BMSEmployee wLoginUser, int wRoleID, List<BMSRoleItem> wUserList,
                                    OutResult<Integer> wErrorCode) {
        wErrorCode.set(0);

        try {

            String wSQLText = "";
            Map<String, Object> wParams = new HashMap<String, Object>();
            // Step01:Delete
            if (wRoleID > 0) {
                wSQLText = StringUtils.Format("delete from {0}.mbs_roleuser", super.getDBName())
                        + " where RoleID=:RoleID";
                wParams.clear();
                wParams.put("RoleID", wRoleID);
                super.update(wSQLText, wParams);
            }

            // Step02:
            for (BMSRoleItem wFunctionRole : wUserList) {
                wSQLText = StringUtils.Format("Insert Into {0}.mbs_roleuser", super.getDBName())
                        + "(RoleID,UserID) " + " Values(:RoleID,:UserID)";
                wParams.clear();
                wParams.put("RoleID", wFunctionRole.RoleID);
                wParams.put("UserID", wFunctionRole.FunctionID);
                super.update(wSQLText, wParams);
            }

        } catch (Exception ex) {

            logger.error("BMS_SaveRoleUserList", ex);
            wErrorCode.set(ExceptionTypes.DBSQL.getValue());
        }
        return wErrorCode.Result;
    }

    public int BMS_SaveRoleUserListByUser(BMSEmployee wLoginUser, int wUserID, List<BMSRoleItem> wUserList,
                                          OutResult<Integer> wErrorCode) {
        wErrorCode.set(0);

        try {

            String wSQLText = "";
            Map<String, Object> wParams = new HashMap<String, Object>();
            // Step01:Delete
            if (wUserID > 0) {
                wSQLText = StringUtils.Format("delete from {0}.mbs_roleuser", super.getDBName())
                        + " where UserID=:UserID";
                wParams.clear();
                wParams.put("UserID", wUserID);
                super.update(wSQLText, wParams);
            }

            // Step02:
            for (BMSRoleItem wFunctionRole : wUserList) {
                wSQLText = StringUtils.Format("Insert Into {0}.mbs_roleuser", super.getDBName())
                        + "(RoleID,UserID) " + " Values(:RoleID,:UserID)";
                wParams.clear();
                wParams.put("RoleID", wFunctionRole.RoleID);
                wParams.put("UserID", wFunctionRole.FunctionID);
                super.update(wSQLText, wParams);
            }

        } catch (Exception ex) {

            logger.error("BMS_SaveRoleUserList", ex);
            wErrorCode.set(ExceptionTypes.DBSQL.getValue());
        }
        return wErrorCode.Result;
    }

    public List<BMSRoleItem> BMS_QueryFunctionListByRoleID(BMSEmployee wLoginUser, int wRoleID,
                                                           OutResult<Integer> wErrorCode) {
        wErrorCode.set(0);
        List<BMSRoleItem> wUserList = new ArrayList<BMSRoleItem>();

        try {

            String wSQLText = StringUtils.Format("Select * from {0}.mbs_rolefunction", super.getDBName())
                    + " where RoleID=:RoleID";

            Map<String, Object> wParams = new HashMap<String, Object>();

            wParams.clear();
            wParams.put("RoleID", wRoleID);
            List<Map<String, Object>> wQueryResultList = super.queryForList(wSQLText, wParams);
            BMSRoleItem wRoleFunction = null;
            for (Map<String, Object> wSqlDataReader : wQueryResultList) {
                wRoleFunction = new BMSRoleItem();

                wRoleFunction.RoleID = StringUtils.parseInt(wSqlDataReader.get("RoleID"));
                wRoleFunction.FunctionID = StringUtils.parseInt(wSqlDataReader.get("FunctionID"));

                wUserList.add(wRoleFunction);
            }

        } catch (Exception ex) {
            wErrorCode.set(ExceptionTypes.DBSQL.getValue());
            logger.error("BMS_QueryFunctionListByRoleID", ex);
        }
        return wUserList;
    }

    public List<BMSRoleItem> BMS_QueryFunctionListByLoginID(BMSEmployee wLoginUser, int wUserID,
                                                            OutResult<Integer> wErrorCode) {
        wErrorCode.set(0);
        List<BMSRoleItem> wFunctionList = new ArrayList<BMSRoleItem>();

        try {

            if (wUserID == BMSEmployee.SysAdmin.ID) {
                wFunctionList = this.BMS_GetFunctionNodeTree(wLoginUser, -1, 1, wErrorCode);
                return wFunctionList;
            }

            String wSQLText = StringUtils.Format(
                    "SELECT t.*,r.Text,r.RoleID as UserID FROM {0}.mbs_rolefunction t "
                            + "left join {0}.mbs_roletree r on t.FunctionID = r.FunctionID"
                            + " left join {0}.mbs_roleuser t1 on t.RoleID=t1.RoleID where t1.UserID=:UserID",
                    super.getDBName());

            Map<String, Object> wParams = new HashMap<String, Object>();

            wParams.clear();
            wParams.put("UserID", wUserID);
            List<Map<String, Object>> wQueryResultList = super.queryForList(wSQLText, wParams);
            for (Map<String, Object> wSqlDataReader : wQueryResultList) {
                BMSRoleItem wRoleFunction = new BMSRoleItem();
                wRoleFunction.FunctionID = StringUtils.parseInt(wSqlDataReader.get("FunctionID"));
                wRoleFunction.RoleID = StringUtils.parseInt(wSqlDataReader.get("RoleID"));
                wRoleFunction.UserID = StringUtils.parseInt(wSqlDataReader.get("UserID"));
                wRoleFunction.Text = StringUtils.parseString(wSqlDataReader.get("Text"));
                wFunctionList.add(wRoleFunction);
            }

        } catch (Exception ex) {
            wErrorCode.set(ExceptionTypes.DBSQL.getValue());
            logger.error("BMS_QueryFunctionListByLoginID", ex);
        }
        return wFunctionList;
    }

    public List<BMSRoleItem> BMS_QueryUserListByRoleID(BMSEmployee wLoginUser, int wRoleID,
                                                       OutResult<Integer> wErrorCode) {
        List<BMSRoleItem> wUserList = new ArrayList<BMSRoleItem>();
        wErrorCode.set(0);

        try {

            String wSQLText = StringUtils.Format("Select r.*,u.name as UserName from {0}.mbs_roleuser r,{1}.mbs_user u",
                    super.getDBName(), super.getDBName())
                    + " where r.RoleID=:RoleID and r.UserID=u.ID";
            Map<String, Object> wParams = new HashMap<String, Object>();

            wParams.clear();
            wParams.put("RoleID", wRoleID);
            List<Map<String, Object>> wQueryResultList = super.queryForList(wSQLText, wParams);
            for (Map<String, Object> wSqlDataReader : wQueryResultList) {
                int wUserID = StringUtils.parseInt(wSqlDataReader.get("UserID"));
                String wUserName = StringUtils.parseString(wSqlDataReader.get("UserName"));
                BMSRoleItem wRoleUser = new BMSRoleItem();
                wRoleUser.RoleID = wRoleID;
                wRoleUser.FunctionID = wUserID;
                wRoleUser.Text = wUserName;

                wUserList.add(wRoleUser);
            }

        } catch (Exception ex) {
            wErrorCode.set(ExceptionTypes.DBSQL.getValue());

            logger.error("BMS_GetUserList", ex);
        }
        return wUserList;
    }

    public List<BMSRoleItem> BMS_QueryUserListByFunctionID(BMSEmployee wLoginUser, int wFunctionID,
                                                           OutResult<Integer> wErrorCode) {

        List<BMSRoleItem> wUserList = new ArrayList<BMSRoleItem>();

        try {

            String wSQLText = "";
            wSQLText = StringUtils.Format(
                    "SELECT t.*,t1.UserID,t2.Name as Text  FROM {0}.mbs_rolefunction t"
                            + " left join {0}.mbs_roleuser t1 on t.RoleID=t1.RoleID"
                            + " left join {0}.mbs_user t2 on t1.UserID=t2.ID where t.FunctionID=:FunctionID",
                    super.getDBName());

            Map<String, Object> wParams = new HashMap<String, Object>();

            wParams.clear();
            wParams.put("FunctionID", wFunctionID);
            List<Integer> wUserID = new ArrayList<Integer>();
            BMSRoleItem wRoleFunction;
            List<Map<String, Object>> wQueryResultList = super.queryForList(wSQLText, wParams);
            for (Map<String, Object> wSqlDataReader : wQueryResultList) {
                wRoleFunction = new BMSRoleItem();
                wRoleFunction.FunctionID = StringUtils.parseInt(wSqlDataReader.get("FunctionID"));
                wRoleFunction.RoleID = StringUtils.parseInt(wSqlDataReader.get("RoleID"));
                wRoleFunction.UserID = StringUtils.parseInt(wSqlDataReader.get("UserID"));
                if (wUserID.contains(wRoleFunction.UserID))
                    continue;
                wRoleFunction.Text = StringUtils.parseString(wSqlDataReader.get("Text"));
                wUserList.add(wRoleFunction);
            }

        } catch (Exception ex) {
            wErrorCode.set(ExceptionTypes.DBSQL.getValue());
            logger.error("BMS_QueryUserListByFunctionID", ex);
        }
        return wUserList;
    }

    public List<BMSRoleItem> BMS_QueryRangeList(BMSEmployee wLoginUser, int wOperatorID, int wFunctionID,
                                                OutResult<Integer> wErrorCode) {
        wErrorCode.set(0);
        List<BMSRoleItem> wRangeList = new ArrayList<BMSRoleItem>();

        try {

            String wSQLText = "";

            wSQLText = StringUtils
                    .Format("SELECT t1.FunctionID ,t2.* ,t3.FunctionID as RangeID,t3.TypeID as RangeType,t4.UserID"
                            + "  FROM {0}.mbs_rolefunction t1,{0}.mbs_role t2,{0}.mbs_rolerange t3,{0}.mbs_roleuser t4 "
                            + " where  t1.RoleID=t2.ID AND t2.ID=t3.RoleID AND t4.RoleID=t2.ID AND t2.Active=1"
                            + " and (:FunctionID<=0 OR t1.FunctionID=:FunctionID)"
                            + " and (:UserID<=0 OR  t4.UserID=:UserID)", super.getDBName());

            Map<String, Object> wParams = new HashMap<String, Object>();

            wParams.clear();
            wParams.put("UserID", wOperatorID);
            wParams.put("FunctionID", wFunctionID);

            wSQLText = this.DMLChange(wSQLText);
            List<Map<String, Object>> wQueryResultList = super.queryForList(wSQLText, wParams);
            for (Map<String, Object> wSqlDataReader : wQueryResultList) {
                BMSRoleItem wRoleFunction = new BMSRoleItem();
                wRoleFunction.RoleID = StringUtils.parseInt(wSqlDataReader.get("ID"));
                wRoleFunction.FunctionID = StringUtils.parseInt(wSqlDataReader.get("RangeID"));
                wRoleFunction.TypeID = StringUtils.parseInt(wSqlDataReader.get("RangeType"));

                wRangeList.add(wRoleFunction);
            }

        } catch (Exception ex) {
            wErrorCode.set(ExceptionTypes.DBSQL.getValue());

            logger.error("BMS_QueryRangeListByOperatorID", ex);
        }
        return wRangeList;
    }

    public List<BMSRoleItem> BMS_QueryRangeListByRoleID(BMSEmployee wLoginUser, int wRoleID,
                                                        OutResult<Integer> wErrorCode) {
        wErrorCode.set(0);
        List<BMSRoleItem> wRangeList = new ArrayList<BMSRoleItem>();

        try {

            String wSQLText = "";
            wSQLText = StringUtils.Format("Select * from {0}.mbs_rolerange", super.getDBName())
                    + " where RoleID=:RoleID";
            Map<String, Object> wParams = new HashMap<String, Object>();

            wSQLText = this.DMLChange(wSQLText);

            wParams.clear();
            wParams.put("RoleID", wRoleID);
            List<Map<String, Object>> wQueryResultList = super.queryForList(wSQLText, wParams);
            for (Map<String, Object> wSqlDataReader : wQueryResultList) {
                // wSqlDataReader\[(\"\w+\")\] wSqlDataReader.get($1)
                int wFunctionID = StringUtils.parseInt(wSqlDataReader.get("FunctionID"));
                int wTypeID = StringUtils.parseInt(wSqlDataReader.get("TypeID"));
                BMSRoleItem wRoleFunction = new BMSRoleItem();
                wRoleFunction.RoleID = wRoleID;
                wRoleFunction.FunctionID = wFunctionID;
                wRoleFunction.TypeID = wTypeID;
                wRangeList.add(wRoleFunction);
            }

        } catch (Exception ex) {
            wErrorCode.set(ExceptionTypes.DBSQL.getValue());
            logger.error("BMS_QueryRangeListByRoleID", ex);
        }
        return wRangeList;
    }

    public List<BMSRoleItem> BMS_GetFunctionNodeTree(BMSEmployee wLoginUser, int wFunctionID, int wActive,
                                                     OutResult<Integer> wErrorCode) {
        List<BMSRoleItem> wFunctionNodeList = new ArrayList<BMSRoleItem>();
        try {

            String wSQLText = "";
            wSQLText = StringUtils
                    .Format("Select * from {0}.mbs_roletree where  (:FunctionID<=0 OR FunctionID=:FunctionID)"
                            + " and (:Active<0 OR Active=:Active) order by OrderID ", super.getDBName());
            Map<String, Object> wParams = new HashMap<String, Object>();
            wParams.put("FunctionID", wFunctionID);
            wParams.put("Active", wActive);
            List<Map<String, Object>> wQueryResultList = super.queryForList(wSQLText, wParams);
            for (Map<String, Object> wSqlDataReader : wQueryResultList) {
                // wSqlDataReader\[(\"\w+\")\] wSqlDataReader.get($1)
                BMSRoleItem wRoleFunction = new BMSRoleItem();
                wRoleFunction.FunctionID = StringUtils.parseInt(wSqlDataReader.get("FunctionID"));
                wRoleFunction.RoleID = StringUtils.parseInt(wSqlDataReader.get("RoleID"));
                wRoleFunction.Text = StringUtils.parseString(wSqlDataReader.get("Text"));
                wRoleFunction.TypeID = StringUtils.parseInt(wSqlDataReader.get("Active"));
                wRoleFunction.UserID = StringUtils.parseInt(wSqlDataReader.get("OrderID"));
                wRoleFunction.Path = StringUtils.parseString(wSqlDataReader.get("Path"));
                wRoleFunction.Remark = StringUtils.parseString(wSqlDataReader.get("Remark"));

                wFunctionNodeList.add(wRoleFunction);
            }

        } catch (Exception ex) {
            wErrorCode.set(ExceptionTypes.DBSQL.getValue());
            logger.error("BMS_GetFunctionNodeTree", ex);
        }
        return wFunctionNodeList;
    }

    public int BMS_CheckPower(BMSEmployee wLoginUser, int wUserID, int wFunctionID, int wRangTypeID, int wObjectID,
                              OutResult<Integer> wErrorCode) {
        int wPower = 0;
        try {
            wErrorCode.set(0);
            if (wUserID == 0 || wFunctionID <= 0 || wUserID == BMSEmployee.SysAdmin.ID
                    || wUserID > Constants.EngineerUserIDMin) {
                return 1;
            }

            String wSQLText = StringUtils.Format(
                    "SELECT t1.FunctionID ,t2.* ,t3.FunctionID as RangeID,t3.TypeID as RangeType,t4.UserID FROM {0}" +
                            ".mbs_rolefunction t1"
                            + " left join {0}.mbs_role t2 on t1.RoleID=t2.ID"
                            + " left join {0}.mbs_rolerange t3 on  t2.ID=t3.RoleID "
                            + " left join {0}.mbs_roleuser t4 on  t4.RoleID=t2.ID "
                            + "	where t2.Active=1 and t1.FunctionID=:FunctionID and t4.UserID=:UserID"
                            + " and (:RangeID<=0 OR t3.FunctionID=:RangeID) "
                            + " and (:RangType<=0 OR t3.TypeID=:RangType) ",
                    super.getDBName());

            Map<String, Object> wParams = new HashMap<String, Object>();
            wParams.put("UserID", wUserID);
            wParams.put("RangeID", wObjectID);
            wParams.put("FunctionID", wFunctionID);
            wParams.put("RangType", wRangTypeID);
            wSQLText = this.DMLChange(wSQLText);
            List<Map<String, Object>> wQueryResultList = super.queryForList(wSQLText, wParams);
            if (wQueryResultList.size() > 0) {
                wPower = 1;

                if (Constants.GetAuthRecord() != null && Constants.GetAuthRecord().ModuleList != null
                        && Constants.GetAuthRecord().ModuleList.size() > 0) {

                    for (BFCModuleAuth wModule : Constants.GetAuthRecord().ModuleList) {
                        if (wFunctionID != wModule.ModuleID)
                            continue;
                        //有模块授权
                        if (wModule.ExpireDate.compareTo(Calendar.getInstance()) < 0) {
                            wPower = 2;
                        }
                    }

                }
            }

        } catch (Exception ex) {
            logger.error("BMS_CheckRangeByAuthorityID", ex);
        }
        return wPower;
    }

    public int BMS_CheckPower(BMSEmployee wLoginUser, int wUserID, String wPath, OutResult<Integer> wErrorCode) {
        return BMS_CheckPower(wLoginUser, wUserID, wPath, 0, 0, wErrorCode);
    }

    public int BMS_CheckPower(BMSEmployee wLoginUser, int wUserID, String wPath, int wTypeID, int wObjectID,
                              OutResult<Integer> wErrorCode) {
        int wPower = 0;
        try {

            if (Constants.GetAuthRecord() != null && Constants.GetAuthRecord().ExpireDate.compareTo(Calendar.getInstance()) <= 0)
                return 2;

            if (Constants.RoleManageEnable <= 0)
                return 1;

            if (wUserID == 0 || StringUtils.isEmpty(wPath) || wUserID == BMSEmployee.SysAdmin.ID
                    || wUserID > Constants.EngineerUserIDMin) {
                return 1;
            }

            String wSQLText = StringUtils.Format(
                    "SELECT t1.FunctionID ,t2.* ,t3.FunctionID as RangeID,t3.TypeID as RangeType,t4.UserID FROM {0}" +
                            ".mbs_rolefunction t1"
                            + " left join {0}.mbs_role t2 on t1.RoleID=t2.ID"
                            + " left join {0}.mbs_rolerange t3 on  t2.ID=t3.RoleID "
                            + " left join {0}.mbs_roleuser t4 on  t4.RoleID=t2.ID "
                            + " inner join {0}.mbs_roletree t5 on  t5.FunctionID=t1.FunctionID "
                            + "	where t2.Active=1 and t5.Path=:Path and t4.UserID=:UserID"
                            + " and (:RangeID<=0 OR t3.FunctionID=:RangeID) "
                            + " and (:RangType<=0 OR t3.TypeID=:RangType) ",
                    super.getDBName());

            Map<String, Object> wParams = new HashMap<String, Object>();
            wParams.put("UserID", wUserID);
            wParams.put("RangeID", wObjectID);
            wParams.put("Path", wPath);
            wParams.put("RangType", wTypeID);
            wSQLText = this.DMLChange(wSQLText);
            List<Map<String, Object>> wQueryResultList = super.queryForList(wSQLText, wParams);
            if (wQueryResultList.size() == 0) {
                wSQLText = StringUtils.Format("SELECT * FROM {0}.mbs_roletree  where Path=:Path ",
                        super.getDBName());
                wQueryResultList = super.queryForList(wSQLText, wParams);

                if (wQueryResultList.size() == 0) {
                    wPower = 1;
                }
            } else {
                wPower = 1;
                if (Constants.GetAuthRecord() != null && Constants.GetAuthRecord().ModuleList != null && Constants.GetAuthRecord().ModuleList.size() > 0) {

                    List<Integer> wFunctionIDList = new ArrayList<>();
                    int wFunctionID = 0;
                    for (Map<String, Object> wQueryResult : wQueryResultList) {
                        wFunctionID = StringUtils.parseInt(wQueryResult.get("FunctionID"));
                        if (wFunctionIDList.contains(wFunctionID))
                            continue;
                        wFunctionIDList.add(wFunctionID);
                    }

                    for (BFCModuleAuth wModule : Constants.GetAuthRecord().ModuleList) {
                        if (!wFunctionIDList.contains(wModule.ModuleID))
                            continue;

                        //有模块授权

                        if (wModule.ExpireDate.compareTo(Calendar.getInstance()) < 0) {
                            wPower = 2;
                        }
                    }

                }
            }

        } catch (Exception ex) {
            logger.error("BMS_CheckPowerByLoginID", ex);
        }
        return wPower;
    }

    public void BMS_UpdateFunctionNodeTree(BMSEmployee wLoginUser, BMSRoleItem wBMSRoleItem,
                                           OutResult<Integer> wErrorCode) {
        wErrorCode.set(0);
        try {

            List<BMSRoleItem> wBMSRoleItemList = this.BMS_GetFunctionNodeTree(wLoginUser, wBMSRoleItem.FunctionID, -1,
                    wErrorCode);

            String wSQLText = "";
            if (wBMSRoleItemList == null || wBMSRoleItemList.size() <= 0) {
                wSQLText = StringUtils.Format(
                        "Insert into {0}.mbs_roletree (FunctionID,RoleID,Text,Active,OrderID,Path,Remark) " +
                                "Values (:FunctionID,:RoleID,:Text,0,:OrderID,:Path,:Remark);",
                        super.getDBName());
            } else {
                wSQLText = StringUtils.Format(
                        "Update {0}.mbs_roletree set RoleID=:RoleID ,Text=:Text,OrderID=:OrderID,Path=:Path," +
                                "Remark=:Remark Where FunctionID=:FunctionID ;",
                        super.getDBName());
            }

            Map<String, Object> wParams = new HashMap<String, Object>();
            wParams.put("FunctionID", wBMSRoleItem.FunctionID);
            wParams.put("RoleID", wBMSRoleItem.RoleID);
            wParams.put("Text", wBMSRoleItem.Text);
            wParams.put("OrderID", wBMSRoleItem.UserID);
            wParams.put("Path", wBMSRoleItem.Path);
            wParams.put("Remark", wBMSRoleItem.Remark);

            SqlParameterSource wSqlParameterSource = new MapSqlParameterSource(wParams);
            super.update(wSQLText, wSqlParameterSource);

        } catch (Exception ex) {
            wErrorCode.set(ExceptionTypes.DBSQL.getValue());
            logger.error("BMS_GetFunctionNodeTree", ex);
        }
    }

    public void BMS_ActiveFunctionNodeTree(BMSEmployee wLoginUser, List<Integer> wFunctionIDList, int wActive,
                                           OutResult<Integer> wErrorCode) {
        try {
            wErrorCode.set(0);

            if (wFunctionIDList == null || wFunctionIDList.size() <= 0) {
                wErrorCode.set(ExceptionTypes.Parameter.getValue());
                return;
            }

            if (wActive != 1)
                wActive = 2;

            String wSQLText = StringUtils.Format(
                    "Update {0}.mbs_roletree set Active=:wActive Where FunctionID IN (:FunctionIDList);",
                    super.getDBName());

            Map<String, Object> wParams = new HashMap<String, Object>();
            wParams.put("wActive", wActive);
            wParams.put("FunctionIDList", StringUtils.Join(",", wFunctionIDList));

            SqlParameterSource wSqlParameterSource = new MapSqlParameterSource(wParams);
            super.update(wSQLText, wSqlParameterSource);

        } catch (Exception ex) {
            wErrorCode.set(ExceptionTypes.DBSQL.getValue());
            logger.error("BMS_ActiveFunctionNodeTree", ex);
        }
    }

    public void BMS_DeleteFunctionNodeTree(BMSEmployee wLoginUser, List<Integer> wFunctionIDList,
                                           OutResult<Integer> wErrorCode) {

        try {
            wErrorCode.set(0);

            if (wFunctionIDList == null || wFunctionIDList.size() <= 0) {
                wErrorCode.set(ExceptionTypes.Parameter.getValue());
                return;
            }

            String wSQLText = StringUtils.Format(
                    "Delete FROM {0}.mbs_roletree  Where ID>0 AND FunctionID IN (:FunctionIDList) AND Active =0 ;",
                    super.getDBName());

            Map<String, Object> wParams = new HashMap<String, Object>();

            wParams.put("FunctionIDList", StringUtils.Join(",", wFunctionIDList));

            SqlParameterSource wSqlParameterSource = new MapSqlParameterSource(wParams);
            super.update(wSQLText, wSqlParameterSource);

        } catch (Exception ex) {
            wErrorCode.set(ExceptionTypes.DBSQL.getValue());
            logger.error("BMS_ActiveFunctionNodeTree", ex);
        }

    }

    public int BMS_MaxFunctionIDNode(BMSEmployee wLoginUser, int wMinFunctionID, int MaxFunctionID,
                                     OutResult<Integer> wErrorCode) {
        int wResult = 0;
        try {
            wErrorCode.set(0);

            String wSQLText = StringUtils.Format(
                    "Select max(FunctionID) from {0}.mbs_roletree where FunctionID between :MinFunctionID AND  " +
                            ":MaxFunctionID ",
                    super.getDBName());
            Map<String, Object> wParams = new HashMap<String, Object>();

            wParams.put("MinFunctionID", wMinFunctionID);
            wParams.put("MaxFunctionID", MaxFunctionID);

            wResult = super.queryForObject(wSQLText, wParams, Integer.class);

        } catch (Exception ex) {
            wErrorCode.set(ExceptionTypes.DBSQL.getValue());
            logger.error("BMS_GetFunctionNodeTree", ex);
        }
        return wResult;
    }

    /**
     * 无限制删除权限树
     */
    public void BMS_DeleteFunctionNodeTree_V1(BMSEmployee wBMSEmployee, List<Integer> wFunctionIDList,
                                              OutResult<Integer> wErrorCode) {
        try {
            if (wFunctionIDList == null || wFunctionIDList.size() <= 0) {
                return;
            }

            String wSQL = StringUtils.Format("DELETE FROM {0}.`mbs_roletree` WHERE FunctionID IN ({1}) AND ID>0;",
                    DBSchemas.Basic.getDBName(), StringUtils.Join(",", wFunctionIDList));

            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wSQL = this.DMLChange(wSQL);

            this.update(wSQL, wParamMap);
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
    }
}
